<html>

<head>
<title>PyDbLite</title>
</head>

<body>

<style type="text/css">
body, td {
	color: #000000;
	background-color: #ffffff;
	font-family: sans-serif;
	font-size: 13;
	}

pre {
	font-family: arial }

li { padding-bottom:10;
	}

.python {
	color:330099;
	font-family: "Courier New";
	}

.console {
	color:white;
	background-color:black;
	font-family: "Courier New";
	padding : 3;
	}

td.navigation
{ background-color: #99ccff;
  font-weight: bold;
  font-family: avantgarde, sans-serif;
  font-size: 110%;
  width: 90%}

td.lnk { background-color: #99ccff;
  font-size: 70%;
    }

ol { margin-left : 20px;
	}

</style>

<table width="100%" cellspacing="0"><tr><td class="navigation" align="center">PyDbLite</td>
<td class="lnk" align="right"></td>
</tr></table>
<p>PyDbLite is a pure-Python in-memory database engine, using Python list
comprehensions as query language, instead of SQL

<p>It consists of one small module, <code>PyDbLite.py</code>. The package also provides two modules,
<code>SQLite.py</code> and <code>MySQL.py</code>. They use SQLite and MySQL backends with the
same Pythonic syntax as the pure-Python PyDbLite engine

<p>To install the package, just <a href="http://sourceforge.net/project/platformdownload.php?group_id=210258">download
it</a> and install it by running <span class="console">>python setup.py install</span>

<p><h2>Pure-Python engine</h2>

<ul>
<li> import class <CODE>Base</CODE> from module PyDbLite : <span class="python">from PyDbLite import Base</span>

<li> create a database instance, passing it a path in the file system : <span class="python">db = Base('dummy')</span>

<li>for a new database, define the field names : <span class="python">db.create('name','age','size')</span>

<br>You don't have to define the field types. PyDbLite will accept any value that
can be serialized by the <CODE>cPickle</CODE> module : strings, Unicode strings, integers,
floats, dates and datetimes (instances of the <CODE>date</CODE> and <CODE>datetime</CODE> classes in the <CODE>datetime</CODE> module), user-defined classes, etc

<li> if the base exists, open it : <span class="python">db.open()</span>

<li> you can pass a parameter "mode" to the <CODE>create()</CODE> method, to specify what
you want to do if the base already exists in the file system
<ul>
<li>mode = "open" : <span class="python">db.create('name','age','size',mode="open")</span>

	opens the database and ignores the field definition

<li> mode = "override" : <span class="python">db.create('name','age','size',mode="override")</span>

	erases the existing base and creates a new one with the field definition

<li> if mode is not specified and the base already exists, an <CODE>IOError</CODE> is raised
</ul>

<li> insert a new record
<ul>
<li> by keywords : <span class="python">db.insert(name='homer',age=23,size=1.84)</span>

<br>If some fields are missing, they are initialized with the value <CODE>None</CODE>

<li> by positional arguments : <span class="python">db.insert('homer',23,1.84)</span>

<br>The arguments must be provided in the same order as in the <CODE>create()</CODE> method
</ul>

<li>save the changes on disk : <span class="python">db.commit()</span>
<br>If you don't commit the changes, the insertion, deletion and update operations
will not be saved on disk. To return to the previous version, just <span class="python">
open()</span> it again (this is equivalent to rollback in transactional
databases)

<li> besides the fields passed to the <CODE>create()</CODE> method, an internal field called <CODE>__id__</CODE> is added. It is a integer which is guaranteed to be unique and unchanged for each record in the base, so that it can be used as the record identifier

<li> another internal field called <CODE>__version__</CODE> is also managed by the database engine. It is a integer which is set to 0 when the record is created, then incremented by 1 each time the record is updated. This is used to detect concurrency control, for instance in a web application where 2 users select the same record and want to update it at the same time

<li>the selection of records uses Python list comprehension syntax :
<br><span class="python">recs = [ r for r in db if 30 > r['age'] >= 18 and r['size'] < 2 ]</span>

<br>returns the records in the base where the age is between 18 and 30, and size is below 2 meters. The record is a dictionary, where the key is the field name and value is the field value

<li> Python generator expression syntax can also be used :
<br><span class="python">for r in (r for r in db if r['name'] in ('homer','marge') ):<br>
&nbsp;&nbsp;&nbsp;&nbsp;do_something_with(r)</span>

<br>iterates on the records where the name is one of 'homer' or 'marge'

<li> to iterate on all the records :
<br><span class="python">for r in db:<br>
&nbsp;&nbsp;&nbsp;&nbsp;do_something_with(r)</span>

<li> a record can be accessed by its identifier : <span class="python">record = db[rec_id]</span>

returns the record such that record['__id__'] == rec_id

<li> finally, a shortcut can be used for simple selections :
<span class="python">db(key1=val1,key2=val2)</span> returns the list of records where the keys take the given value. It is equivalent to <span class="python">[ r for r in db if r["key1"]==val1 and r["key2"]==val2]</span>, but much more concise

<li>to speed up selections, an index can be created on a field : <span class="python">db.create_index('age')</span>

<br>When an index is created, the database instance has an attribute (here <CODE>_age</CODE> : note the heading underscore, to avoid name conflicts with internal names). This attribute is a dictionary-like object, where keys are the values taken by the field, and values are the records whose field values are egal to the key :
<br><span class="python">records = db._age[23]</span> returns the list of records with age == 23

<br>If no record has this value, lookup by this value returns an empty list

<br>The index supports iteration on the field values, and the <CODE>keys()</CODE> method
returns all existing values for the field

<li>number of records in the base : <span class="python">len(db)</span>

<li>to delete a record : <span class="python">db.delete(record)</span> or, if you know the record identifier : <span class="python">del db[rec_id]</span>

<li>to delete a list of records : <span class="python">db.delete(list_of_records)</span>

<br><CODE>list_of_records</CODE> can be any iterable (list, tuple, set, etc) yielding records

<li>to update a record : <span class="python">db.update(record,age=24)</span>

<li>to add a new field to an existing base and specify a default value : <span class="python">db.add_field('new_field'[,default=v])</span>. If no default is provided, the field value is <CODE>None</CODE>

<li>to drop an existing field : <span class="python">db.drop_field('name')</span>

<li>to get the list of fields : <span class="python">db.fields</span>

</ul>

<a name="sqlite"><p><h2>SQLite adapter</h2>
<p>The only difference with the pure-Python module is the syntax to identify a <code>Base</code> and the need to specify field types on base creation
<ul>
<li>import the class <code>Base</code> :
<span class="python">from PyDbLite.SQLite import Base</span>
<li>connect to the SQLite database :
<span class="python">connection = sqlite.connect("test")</span>
<li>to create a <code>Base</code> instance (a table in the SQLite database) you pass the connection as argument : <span class="python">db = Base('dummy',connection)</code>
<li>to create the base you must specify an SQLite field type : NULL, INTEGER, REAL, TEXT
or BLOB : <span class="python">db.create(('name','TEXT'),('age',"INTEGER'),('size','REAL'))</span>
<p>For convenience, you can also use the types DATE and DATETIME (or TIMESTAMP), the package will
transparently manage the conversions between the <code>datetime.date</code> and
<code>datetime.datetime</code> and the TEXT type
</ul>
<p>For record insertion, selection, update and deletion, the syntax is the same as above. The only difference is that you can't use the <code>drop_field()</code> method, since dropping fields is not supported by SQLite
<p>The <code>Base</code> instance has an attribute <code>cursor</code>, so you can also execute
SQL expressions by <span class="python">db.cursor.execute(some_sql)</span> and get the result
by <span class="python">results = db.cursor.fetchall()</span>

<p><a name="mysql"><h2>MySQL adapter</h2>
<p>The only difference with the pure-Python module is the syntax to identify a <code>Base</code> and the need to specify field types on base creation
<ul>
<li>import the class <code>Base</code> :
<span class="python">from PyDbLite.MySQL import Base</span>
<li>connect to the SQLite database :
<p><span class="python">import MySQLdb<br>
 connection = MySQLdb.connect("localhost","root","admin")<br>
 connection.cursor().execute("USE test")
 </span>

<li>pass the connection as argument to Base creation : <span class="python">db = Base('dummy',connection)</code>
<li>to create a new base (a table in the MySQL database),specify a valid MySQL field type :
<span class="python">db.create(('name','INTEGER'),('age',"INTEGER'),('size','REAL'))</span>
</ul>

<p>For record insertion, selection, update and deletion, adding or dropping fields,
the syntax is the same as above
<p>The <code>Base</code> instance has an attribute <code>cursor</code>, so you can also execute
SQL expressions by <span class="python">db.cursor.execute(some_sql)</span> and get the result
by <span class="python">results = db.cursor.fetchall()</span>


</body>
</html>